package com.scalar.forecast.forecastReports.actions;

import com.scalar.chiptrack.ChipTrackExtendedAction;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.commons.ExcelColumnProperties;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.reports.DataDumpReportsHelper;
import com.scalar.chiptrack.operations.reports.DBColumn;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.forecast.forecastReports.AnalysisReport;
import com.scalar.ScalarException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * Action class to handle the forecast reports request actions for the IKANOS.
 */
public class IkanosForecastReportsAction extends ChipTrackExtendedAction {
	private final String ACTION_SALES = "SALES";
	private final String ACTION_OPERATIONS = "OPERATIONS";
	private final String ACTION_SALES_FORECAST = "salesForecast";
	private final String ACTION_SALES_FORECAST_TEMPLATE = "salesForecastTemplate";
	private final String ACTION_SALES_FORECAST_GM_CHIP = "salesForecastTemplateGmChip";
	private final String ACTION_VIEW_COMPARE = "viewCompare";
	private final String ACTION_VIEW_FORECAST = "viewForecast";
	private final String ACTION_COMPARE_FORECAST = "compareForecast";

	public ActionForward executeRequest(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception {
		String action = request.getParameter(Constants.REQUEST_PARAM_ACTION);
		if (ACTION_VIEW_COMPARE.equalsIgnoreCase(action)) {
			ArrayList<AnalysisReport> reportsList = getForecastReportsList();

			request.setAttribute("reportsList", reportsList);
			return mapping.findForward("viewCompare");
		} else {
			ArrayList reportDataList = getReportDataList(action, request);
			List headings = (List)reportDataList.get(0);
			List<ExcelColumnProperties> list = getColumnProperties(headings);

			String fileName = computeFileName(action);
			Workbook workbook = DataDumpReportsHelper.getWorkbook(reportDataList, fileName, null, null, list);
			if(ACTION_COMPARE_FORECAST.equalsIgnoreCase(action)) {
				//todo need to add the timestamp headers in the report
				//add time stamp headers
				/*Sheet sheet = workbook.getSheetAt(0);
				Row row = sheet.getRow(1);
				if(row == null) {
					row = sheet.createRow(1);
				}
				ExcelUtils excelUtils = new ExcelUtils(workbook);*/
				//excelUtils.setCellValue();
			}
			fileName = fileName + "_" + DateUtil.getTimeStamp();

			ExcelUtils.generateExcelReport(workbook, fileName, response);

			return null;
		}
	}

	private ArrayList getReportDataList(String action, HttpServletRequest request) throws Exception {
		Connection conn = null;
		CallableStatement cstmt = null;
		ResultSet rset = null;
		ArrayList reportDataList = new ArrayList();
		try {
			conn = DataSource.getConnection();

			if (ACTION_SALES.equalsIgnoreCase(action) ||
					ACTION_OPERATIONS.equalsIgnoreCase(action) ||
					ACTION_SALES_FORECAST.equalsIgnoreCase(action) ||
					ACTION_SALES_FORECAST_TEMPLATE.equalsIgnoreCase(action) ||
					ACTION_SALES_FORECAST_GM_CHIP.equalsIgnoreCase(action)
					) {
				cstmt = conn.prepareCall("{ CALL SP_GENERATE_FORECAST_REPORT( ? ) }");
				cstmt.setString(1, action);
				rset = cstmt.executeQuery();
				reportDataList = ExcelUtils.getAllResultSetRows(rset, true);
			} else if ( ACTION_VIEW_FORECAST.equalsIgnoreCase(action)) {
				String transactionId = request.getParameter("transactionId");
				cstmt = conn.prepareCall("{ CALL SP_VIEW_FORECAST( ? ) }");
				cstmt.setString(1, transactionId);
				rset = cstmt.executeQuery();
				reportDataList = ExcelUtils.getAllResultSetRows(rset, true);
			} else if (ACTION_COMPARE_FORECAST.equalsIgnoreCase(action)) {
				String transactionId1 = request.getParameter("transactionId1");
				String transactionId2 = request.getParameter("transactionId2");

				cstmt = conn.prepareCall("{ CALL SP_VIEW_FORECAST( ? ) }");
				cstmt.setString(1, transactionId1);
				rset = cstmt.executeQuery();
				ArrayList report1DataList = ExcelUtils.getAllResultSetRows(rset, true);

				cstmt.close();
				cstmt = null;

				cstmt = conn.prepareCall("{ CALL SP_VIEW_FORECAST( ? ) }");
				cstmt.setString(1, transactionId2);
				rset = cstmt.executeQuery();
				ArrayList report2DataList = ExcelUtils.getAllResultSetRows(rset, true);

				reportDataList = getCompareReportDataList(report1DataList, report2DataList);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException ignore) {
				}
			}
			if (cstmt != null) {
				try {
					cstmt.close();
				} catch (SQLException ignore) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ignore) {
				}
			}
		}
		return reportDataList;
	}

	private ArrayList<ExcelColumnProperties> getColumnProperties(List headings) {
		ArrayList<ExcelColumnProperties> list = new ArrayList<ExcelColumnProperties>();


		for (Object headingObj : headings) {
			String headingName = (String) headingObj;
			if (headingName.equalsIgnoreCase("Samples")) {
				list.add(new ExcelColumnProperties(headingName, ExcelColumnProperties.CELL_STYLE_CURRENCY_FORMATTED_$, false));
			} else if (headingName.startsWith("Q")) {
				if (headingName.endsWith("ASP") || headingName.endsWith("ASP_") || headingName.endsWith("ASP__") ||
						headingName.endsWith("Gross Revenue") || headingName.endsWith("Gross Revenue_") || headingName.endsWith("Gross Revenue__") ||
						headingName.endsWith("Rebates") || headingName.endsWith("Rebates_") || headingName.endsWith("Rebates__") ||
						headingName.endsWith("Material Costs") || headingName.endsWith("Material Costs_") || headingName.endsWith("Material Costs__") ||
						headingName.endsWith("Royalty") || headingName.endsWith("Royalty_") || headingName.endsWith("Royalty__") ||
						headingName.endsWith("Net ASP") || headingName.endsWith("Net ASP_") || headingName.endsWith("Net ASP__") ||
						headingName.endsWith("Net Revenue") || headingName.endsWith("Net Revenue_") || headingName.endsWith("Net Revenue__") ||
						headingName.endsWith("OH") || headingName.endsWith("OH_") || headingName.endsWith("OH__") ||
						headingName.endsWith("COGS/Unit") || headingName.endsWith("COGS/Unit_") || headingName.endsWith("COGS/Unit__") ||
						headingName.endsWith("COGS") || headingName.endsWith("COGS_") || headingName.endsWith("COGS__") ||
						headingName.endsWith("GM") || headingName.endsWith("GM_") || headingName.endsWith("GM__") ||
						headingName.endsWith("NM") || headingName.endsWith("NM_") || headingName.endsWith("NM__")
						) {
					list.add(new ExcelColumnProperties(headingName, ExcelColumnProperties.CELL_STYLE_CURRENCY_FORMATTED_$, true));
				} else if (headingName.endsWith("GM%") || headingName.endsWith("GM%_") || headingName.endsWith("GM%__") ||
						headingName.endsWith("NM%") || headingName.endsWith("NM%_") || headingName.endsWith("NM%__")
						) {
					list.add(new ExcelColumnProperties(headingName, ExcelColumnProperties.CELL_STYLE_FLOAT_PER, true));
				} else if (headingName.endsWith("Port Volume__")) {
					list.add(new ExcelColumnProperties(headingName, ExcelColumnProperties.CELL_STYLE_FLOAT, true));
				} else if (headingName.endsWith("Volume__")) {
					list.add(new ExcelColumnProperties(headingName, ExcelColumnProperties.CELL_STYLE_NUMBER, true));
				}
			}
		}

		return list;
	}

	private String computeFileName(String action) {
		String fileName = null;
		if (ACTION_SALES.equalsIgnoreCase(action)) {
			fileName = "sales";
		} else if (ACTION_OPERATIONS.equalsIgnoreCase(action)) {
			fileName = "ops";
		} else if (ACTION_SALES_FORECAST.equalsIgnoreCase(action)) {
			fileName = "Sales Forecast";
		} else if (ACTION_SALES_FORECAST_TEMPLATE.equalsIgnoreCase(action)) {
			fileName = "Sales Forecast Template";
		} else if (ACTION_SALES_FORECAST_GM_CHIP.equalsIgnoreCase(action)) {
			fileName = "Sales Forecast & GM-Chip";
		} else if(ACTION_VIEW_FORECAST.equalsIgnoreCase(action)) {
			fileName = "Historical Forecast View";
		} else if(ACTION_COMPARE_FORECAST.equalsIgnoreCase(action)) {
			fileName = "Historical Forecast Compare Report";
		} else {
			fileName = "Sales Forecast";
		}

		return fileName;
	}

	private ArrayList<AnalysisReport> getForecastReportsList() throws ScalarException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		ArrayList<AnalysisReport> reportList = new ArrayList<AnalysisReport>();
		try {
			conn = DataSource.getConnection();

			String query = "SELECT TRANSACTION_ID FROM (\n" +
					"SELECT DISTINCT TRANSACTION_ID FROM FCENTRYMASTER_ADDITIONAL_DATA_TABLE\n" +
					"UNION\n" +
					"SELECT DISTINCT TRANSACTION_ID FROM FCENTRYMASTER_ADDITIONAL_DATA_HISTORY_TABLE) AS XYZ\n" +
					"ORDER BY TRANSACTION_ID DESC";

			pstmt = conn.prepareStatement(query);
			rset = pstmt.executeQuery();

			while (rset.next()) {
				AnalysisReport analysisReport = new AnalysisReport();

				analysisReport.setTimestamp(new Date(rset.getLong("TRANSACTION_ID")));

				reportList.add(analysisReport);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
			throw new ScalarException(e.getMessage(), e);
		}
		finally {
			if (rset != null) {
				try {
					rset.close();
				} catch (SQLException ignore) {
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException ignore) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ignore) {
				}
			}
		}
		return reportList;
	}

	private ArrayList getCompareReportDataList(ArrayList report1DataList, ArrayList report2DataList) {
		List headings1 = (List)report1DataList.get(0);
		List headings2 = (List)report2DataList.get(0);
		ArrayList finalHeadings = new ArrayList();
		ArrayList diffFormulaList = new ArrayList();
		computeHeadingsAndDiffFormulas(headings1, headings2, finalHeadings, diffFormulaList);
		report1DataList.remove(0);
		report1DataList.add(0, finalHeadings);
		report2DataList.remove(0);
		for(int i=1; i < report1DataList.size(); i++) {
			List row = (List)report1DataList.get(i);
			updateRow(row, report2DataList, diffFormulaList, headings1);
		}
		if(report2DataList.size() > 0) {
			//todo need to add these additional projects data to the report data list
		}
		return report1DataList;
	}

	private void computeHeadingsAndDiffFormulas(List headings1, List headings2, List finalHeadings, List diffFormulaList) {
		finalHeadings.addAll(headings1);
		for(Object headingObj : headings2) {
			String heading2 = (String)headingObj;
			if(heading2.startsWith("Q")) {
				finalHeadings.add(heading2+"_");
			}
		}

		for(Object headingObj : headings1) {
			String heading1 = (String)headingObj;
			if(heading1.startsWith("Q")) {
				finalHeadings.add(heading1+"__");
				String diffFormula = "NA";
				if(headings2.indexOf(heading1) >= 0) {
					diffFormula = "${"+ heading1 +"}" + "-" + "${"+ heading1+"_}";
				}
				diffFormulaList.add(new DBColumn(heading1+"__", diffFormula, java.sql.Types.VARCHAR));
			}
		}
	}
	private void updateRow(List row, List report2DataList, List diffFormulaList, List headings1) {
		List row2 = getSimilarRow(headings1, row, report2DataList);
		if(row2 != null) {
			diffFormulaList.remove(row2);
			for(Object dbColumnObj: row2) {
				DBColumn dbColumn = (DBColumn)dbColumnObj;
				if(dbColumn.getColName().startsWith("Q1") || dbColumn.getColName().startsWith("Q2") || dbColumn.getColName().startsWith("Q3") || dbColumn.getColName().startsWith("Q4")) {
					row.add(dbColumn);
				}
			}
			row.addAll(diffFormulaList);
		}
	}

	private List getSimilarRow(List headings1, List row1, List report2DataList) {
		String proj1 = (String)((DBColumn)row1.get(headings1.indexOf("Chipset Part Name"))).getColValObj();
			proj1 += "_" + ((DBColumn)row1.get(headings1.indexOf("Carrier"))).getColValObj();
			proj1 += "_" + ((DBColumn)row1.get(headings1.indexOf("Customer Name"))).getColValObj();
			proj1 += "_" + ((DBColumn)row1.get(headings1.indexOf("ODM"))).getColValObj();
			proj1 += "_" + ((DBColumn)row1.get(headings1.indexOf("OEM"))).getColValObj();

		for(Object rowColumnObj: report2DataList) {
			List dbColumnList = (List)rowColumnObj;
			String proj2 = (String)((DBColumn)dbColumnList.get(headings1.indexOf("Chipset Part Name"))).getColValObj();
			proj2 += "_" + ((DBColumn)dbColumnList.get(headings1.indexOf("Carrier"))).getColValObj();
			proj2 += "_" + ((DBColumn)dbColumnList.get(headings1.indexOf("Customer Name"))).getColValObj();
			proj2 += "_" + ((DBColumn)dbColumnList.get(headings1.indexOf("ODM"))).getColValObj();
			proj2 += "_" + ((DBColumn)dbColumnList.get(headings1.indexOf("OEM"))).getColValObj();
			if(proj1.equalsIgnoreCase(proj2)) {
				report2DataList.remove(rowColumnObj);
				return (List)rowColumnObj;
			}
		}

		return null;
	}
}
